create or replace package HR_UNITTEST is

  TYPE RETURN_TABLE_TYPE IS REF CURSOR;

  PROCEDURE SP_GET_ADDRESS_SORTBY_LOC(REGION_CODE  NUMBER,
                                    RETURN_TABLE OUT RETURN_TABLE_TYPE);

  PROCEDURE SP_GET_ADDRESS_SORTBY_ZIP(REGION_CODE  NUMBER,
                                    RETURN_TABLE OUT RETURN_TABLE_TYPE);

  PROCEDURE SP_GET_ITALY_ADDRESS(RETURN_TABLE OUT RETURN_TABLE_TYPE);
  
  function FUN_GET_UPPER_STR(Name in varchar2) return varchar2;

end HR_UNITTEST;
/
create or replace package body HR_UNITTEST is

  PROCEDURE SP_GET_ADDRESS_SORTBY_LOC(REGION_CODE  NUMBER,
                                          RETURN_TABLE OUT RETURN_TABLE_TYPE) is
  begin
    OPEN RETURN_TABLE FOR
      SELECT L.*, C.*
        FROM LOCATIONS L, COUNTRIES C
       WHERE L.COUNTRY_ID = C.COUNTRY_ID
         AND C.COUNTRY_NAME = COUNTRY_NAME
         AND C.REGION_ID = REGION_CODE
       ORDER BY L.LOCATION_ID;   
  end;

  PROCEDURE SP_GET_ADDRESS_SORTBY_ZIP(REGION_CODE  NUMBER,
                                        RETURN_TABLE OUT RETURN_TABLE_TYPE) is
  begin
    OPEN RETURN_TABLE FOR
      SELECT L.*, C.*
        FROM LOCATIONS L, COUNTRIES C
       WHERE L.COUNTRY_ID = C.COUNTRY_ID
         AND C.COUNTRY_NAME = COUNTRY_NAME
         AND C.REGION_ID = REGION_CODE
       ORDER BY L.POSTAL_CODE;  
  end;

  
  PROCEDURE SP_GET_ITALY_ADDRESS(RETURN_TABLE OUT RETURN_TABLE_TYPE) is
  begin
    OPEN RETURN_TABLE FOR
      SELECT L.*, C.*
        FROM LOCATIONS L, COUNTRIES C
       WHERE L.COUNTRY_ID = C.COUNTRY_ID
         AND C.COUNTRY_NAME = COUNTRY_NAME
         AND C.COUNTRY_ID = 'IT';
  END;

  FUNCTION FUN_GET_UPPER_STR(NAME IN VARCHAR2) RETURN VARCHAR2 
  IS 
   RESULT VARCHAR2(100);
  BEGIN
    RESULT := UPPER(NAME);    
    RETURN(RESULT);
  END  ;


 
end HR_UNITTEST;
/
